SQL service stopped Started event ID finding

Here’s a fantastic PowerShell script that can simplify our daily tasks by helping us search for events in the Windows Event Viewer. To use this script, open PowerShell ISE and run it. The output will be generated in the specified location. In this example, we’ve chosen the C:\Results folder, but feel free to customize it according to your requirements. You can also add or remove event IDs as needed. The script will create a CSV file that we can review

Code shown as follows:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869$ErrorActionPreference = "Continue"

$startDate = (Get-Date).AddDays(-30)  # Adjust as needed
$endDate = Get-Date

$filterHash = @{
    LogName = @('System', 'Application')
    ID = @(7036, 17162, 17163, 17165)
    StartTime = $startDate
    EndTime = $endDate
}

try {
    $events = Get-WinEvent -FilterHashtable $filterHash -ErrorAction Stop
}
catch {
    Write-Warning "Error retrieving events: $_"
    $events = @()
}

if ($events.Count -gt 0) {
    $results = $events | ForEach-Object {
        $eventMessage = $_.Message.Trim()
        $sqlRelated = $eventMessage -match '(MSSQLSERVER|SQL Server \(|SQL Server Agent)'
        
        if ($sqlRelated -or $_.Id -in @(17162, 17163, 17165)) {
            [PSCustomObject]@{
                TimeCreated = $_.TimeCreated
                EventID = $_.Id
                LogName = $_.LogName
                Message = $eventMessage
                Level = $_.LevelDisplayName
                ServerName = $_.MachineName
                Service = if ($_.Id -eq 7036) {
                    if ($eventMessage -match 'The (.*) service') { $matches[1] }
                    else { "Unknown Service" }
                } elseif ($_.ProviderName -eq 'MSSQLSERVER') {
                    "SQL Server"
                } else {
                    "SQL Related Service"
                }
                State = if ($_.Id -eq 7036) {
                    if ($eventMessage -match 'entered the (.*) state') { $matches[1] }
                    else { "Unknown State" }
                } else { 
                    switch ($_.Id) {
                        17162 { "Started" }
                        17163 { "Stopped" }
                        17165 { "Terminating" }
                        default { "State Change" }
                    }
                }
                UserName = $_.UserId
            }
        }
    }

    $exportPath = "C:\Results\SQLServerCombinedEvents.csv"
    $results | 
        Where-Object { $_ -ne $null } |
        Sort-Object TimeCreated -Descending | 
        Export-Csv -Path $exportPath -NoTypeInformation

    Write-Host "SQL Server events exported to $exportPath"
}
else {
    Write-Warning "No events found matching the specified criteria."
}